﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using EvaluationSystem.Exceptions;
using System.Data;

namespace EvaluationSystem.DAO
{
    public class DAO
    {
        protected string connectionString = String.Empty;
        SqlConnection connection = null;
        
        public DAO()
        {
            if (ConfigurationManager.ConnectionStrings["ConnectionString"] != null)
            {
                connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            }
            else
            {
                throw new ConnectionStringEmptyException();
            }
        }

        protected void OpenConnection()
        {
            if (connection == null)
            {
                connection = new SqlConnection(this.connectionString);
            }
            if (connection.State == System.Data.ConnectionState.Closed)
            {
                connection.Open();
            }
        }

        protected void CloseConnection()
        {
            if (connection != null)
            {
                if (connection.State != System.Data.ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }

        private SqlCommand CreateCommand()
        {
            OpenConnection();
            return connection.CreateCommand();
        }

        protected DataTable ExcuteCommandDataTable(string commandText, Dictionary<string,object> parameters)
        {
            DataTable dt = new DataTable();
            try
            {
                SqlCommand command = CreateCommand();
                command.CommandText = commandText;
                if (parameters != null)
                {
                    foreach (string key in parameters.Keys)
                    {
                        command.Parameters.Add(new SqlParameter(key, parameters[key]));
                    }
                }
                SqlDataAdapter sda = new SqlDataAdapter(command);
                sda.Fill(dt);
                command.Dispose();
                sda.Dispose();
                return dt;
            }
            finally
            {
                CloseConnection();
            }
        }

        protected int ExcuteCommandNonQuery(string commandText, Dictionary<string, object> parameters)
        {
            SqlCommand command = CreateCommand();
            try
            {
                command.CommandText = commandText;
                if (parameters != null)
                {
                    foreach (string key in parameters.Keys)
                    {
                        command.Parameters.Add(new SqlParameter(key, parameters[key]));
                    }
                }
                return command.ExecuteNonQuery();
            }
            finally
            {
                command.Dispose();
                CloseConnection();
            }
        }
    }

    
}